import sqlite3
import ast

def update_table_with_parsed_data(db_path):
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    parsed_data={}

    try:
        # 创建新列（如果尚未存在的话）
        try:
            c.execute('''
                ALTER TABLE parsed_sensor_info
                ADD COLUMN front_and_rear_pitch_angle TEXT
            ''')
        except sqlite3.OperationalError:
            # 列已存在，跳过
            pass

        try:
            c.execute('''
                ALTER TABLE parsed_sensor_info
                ADD COLUMN left_and_right_tilt_angle TEXT
            ''')
        except sqlite3.OperationalError:
            # 列已存在，跳过
            pass

        # 从原始列读取数据
        c.execute('SELECT id, sensor_value FROM parsed_sensor_info')
        rows = c.fetchall()

        for row in rows:
            record_id, sensor_value = row


            # 确保 sensor_value 是字典
            if isinstance(sensor_value, dict):
                parsed_data = ast.literal_eval(sensor_value)
            else:
                '''print(f"警告: 记录ID {record_id} 的 sensor_value 不是字典。")
                parsed_data = {}'''

            # 更新表中的其他列
            c.execute('''
                UPDATE parsed_sensor_info
                SET front_and_rear_pitch_angle = ?,
                    left_and_right_tilt_angle = ?
                WHERE id = ?
            ''', (
                parsed_data.get('front_and_rear_pitch_angle', ''),
                parsed_data.get('left_and_right_tilt_angle', ''),
                record_id
            ))

        conn.commit()
        print("数据更新完成")

    except sqlite3.Error as e:
        print(f"数据库操作出现错误: {e}")

    finally:
        conn.close()


if __name__ == '__main__':
    db_path = '/Users/tancongyuan/desktop/sensor_data.db'  # 替换为你的数据库路径
    update_table_with_parsed_data(db_path)